package com.example.fw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.obj.ContactObject;
import com.example.obj.ContactSet;
import com.example.obj.GroupObject;
import com.example.obj.GroupSet;

public class DataBaseHelper {

	private Connection conn;
	
	public DataBaseHelper(ApplicationManager manager, String url) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(url);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public GroupSet getGroups() {
		GroupSet groups = new GroupSet();
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT group_id,group_name,group_header,group_footer FROM group_list");
			while (res.next()) {
				GroupObject group = new GroupObject()
					.setId(res.getString(1))
					.setName(res.getString(2))
					.setHeader(res.getString(3))
					.setFooter(res.getString(4));
				groups.add(group);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return groups;
	}
	
	public ContactSet getContacts() {
		ContactSet contacts = new ContactSet();
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT id, firstname, lastname, home FROM addressbook");
			while (res.next()) {
				ContactObject contact = new ContactObject()
					.setId(res.getString(1))
					.setFirstName(res.getString(2))
					.setLastName(res.getString(3))
					.setPhoneHome(res.getString(4));
				contacts.add(contact);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return contacts;
	}
	
	public List<ContactObject> getContactList() {
		List<ContactObject> contacts = new ArrayList<ContactObject>();
		Statement st = null;
		ResultSet res = null;
		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT firstname, lastname, home FROM addressbook");
			while (res.next()) {
				ContactObject contact = new ContactObject()
					.setFirstName(res.getString(1))
					.setLastName(res.getString(2))
					.setPhoneHome(res.getString(3));
				contacts.add(contact);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return contacts;
	}
}
